#   @File Name: demo.py
#   @brief  基于Python的绿色砂石建材项目多文件多工作簿多类型条目的自动合并
#   @Author : Maxin email:917932342@qq.com
#   @Version : 1.0
#   @Creat Date : 2023-08-25 
#   @copyright Copyright (c) 2023 Co., Ltd. All rights reserved.

import pandas as pd
import numpy as np
import argparse
import re
import time
import warnings


warnings.filterwarnings("ignore",category=Warning)

# np.warnings.filterwarnings('ignore', category=np.VisibleDeprecationWarning)
# np.seterr(divide='ignore')



target_sheet_names = ['表B.4 矿山开拓运输及开采工程建筑工程估算表','表B.8 砂石工厂工程建筑工程估算表']
source_sheet_names = ['表-04 分部分项工程量清单与计价表','建筑工程概算表']



def main(args):

    # 预处理目标Excel文件
    categories = {}
    Target_DataFrames = {}
    Target_Excel = pd.read_excel(args.target_excel_name,sheet_name=None,header=[0,1])
    for idx, item in enumerate(target_sheet_names):
        columns = [label[1] for label in Target_Excel[item].columns.values]
        Target_Excel[item].columns = columns
        key = re.search(r'[^\s][\u4E00-\u9FA5]+',item).group().replace('建筑工程估算表','')
        # Target_DataFrames[key] = Target_Excel[item].loc[:,Target_Excel[item].columns.values[1:-2]]
        Target_DataFrames[key] = Target_Excel[item].loc[:,Target_Excel[item].columns.values[:-2]]
        # categories[key] = list(Target_DataFrames[key].loc[1:,'工程或费用名称'].dropna())
        categories[key] = list(Target_DataFrames[key].loc[1:,'工程或费用名称'].dropna())



    # 预处理源Excel文件
    miner_DataFrames = {}
    sand_DataFrames = {}
    source_DataFrames = {}
    for file_name,sheet_name in zip(args.source_excel_names,source_sheet_names):

        install_index = []

        source_df = pd.read_excel(file_name,sheet_name,header=[0,1])
        source_df.dropna(axis=1,how='all',inplace=True)

        if len(source_df.columns.values)>6:
            # source_df = source_df.loc[:,source_df.columns.values[2:-1]]
            source_df = source_df.loc[:,source_df.columns.values[1:-1]]
        else:
            # source_df = source_df.loc[:,source_df.columns.values[1:-1]]
            source_df = source_df.loc[:,source_df.columns.values[:-1]]

        columns = Target_DataFrames[list(categories.keys())[0]].columns.values

        source_df.columns = columns


        split_start_name = list(categories.keys())[0]
        split_end_name = list(categories.keys())[1]

        split_start_index = source_df.loc[source_df['工程或费用名称']==split_start_name,:].index.values
        split_end_index = source_df.loc[source_df['工程或费用名称']==split_end_name,:].index.values
        
        # 判断工作表中是否存在设备及安装工程的内容，如果有将其子表删除
        if '设备及安装工程' in list(source_df['工程或费用名称']):
            install_index = source_df.loc[source_df['工程或费用名称']=='设备及安装工程',:].index.values


        if len(install_index)>0:
            miner_DataFrames[file_name] = source_df.loc[int(split_start_index+2):np.min(install_index)-1,:]
            sand_DataFrames[file_name] = source_df.loc[int(split_end_index+2):np.max(install_index)-1,:]
        else:
            miner_DataFrames[file_name] = source_df.loc[int(split_start_index+2):int(split_end_index-1),:]
            sand_DataFrames[file_name] = source_df.loc[int(split_end_index+1):,:]

        source_DataFrames[split_start_name] = miner_DataFrames
        source_DataFrames[split_end_name] = sand_DataFrames


    saved_targetDataFrame = {}

    # 开始合并
    excelWriter = pd.ExcelWriter('./test.xlsx')
    for item_name,merged_items in categories.items():
        next_item = ""
        is_begin = True # 判断是否是刚打开没经过合并的dataframe
        selected_target_dataframe = pd.DataFrame()
        for idx, cur_item in enumerate(merged_items[:-1]):
            next_item = merged_items[idx+1]
            if is_begin:
                target_begin_index = Target_DataFrames[item_name].loc[Target_DataFrames[item_name]['工程或费用名称']==cur_item,:].index.values
                selected_target_dataframe = Target_DataFrames[item_name].loc[:int(target_begin_index),:]
            else:
                selected_target_dataframe = pd.concat([selected_target_dataframe,Target_DataFrames[item_name].loc[Target_DataFrames[item_name]['工程或费用名称']==cur_item,:]],axis=0,ignore_index=True)


            for proj_name,proj_df in source_DataFrames[item_name].items():

                source_begin_index = proj_df.loc[proj_df['工程或费用名称']==cur_item,:].index.values

                if source_begin_index.size>0:
                    source_end_index = proj_df.loc[proj_df['工程或费用名称']==next_item,:].index.values
                    if source_end_index.size==0:
                        source_end_index = proj_df.loc[proj_df['工程或费用名称']==merged_items[idx+2],:].index.values
                else:
                    source_end_index = []


                if int(source_begin_index)+1==int(source_end_index):  # 如果开始索引+1=终止索引，代表该项目下没有内容，不需要进行合并
                    continue
                selected_source_dataframe = proj_df.loc[int(source_begin_index)+1:int(source_end_index)-1,:]
                selected_target_dataframe = pd.concat([selected_target_dataframe,selected_source_dataframe],axis=0,ignore_index=True)

                is_begin = False

        selected_target_dataframe.insert(5,column='合计（万元）',value=pd.Series(dtype=float))
        selected_target_dataframe.to_excel(excelWriter,sheet_name=item_name,index=False)
    

    excelWriter.close()



if __name__ =="__main__":
    parser = argparse.ArgumentParser(description='Merge Item From Diffrent Sheet')
    parser.add_argument('--source_excel_names',nargs='+',default=[],help='Source Excel Files need to be merged.')
    parser.add_argument('--target_excel_name',type=str,default='',help='Target Excel File. ')
    args = parser.parse_args()

    start_time = time.time()
    
    main(args)

    end_time = time.time()

    print(f"累计耗时：{end_time-start_time}")
